use serde::Serialize;
use sqlx::Executor;
use sqlx::Row;
use crate::error::new_err;
use crate::web::download::new_download;
use crate::web::download::Download;
use crate::{error::{AppErr, SqlxErr}, utils::current_timestamp};

use super::get_pool;


const CREATE_SQL: &'static str = r#"
CREATE TABLE IF NOT EXISTS tb_firm (
    id INTEGER PRIMARY KEY AUTOINCREMENT, 
    name TEXT NOT NULL, 
    create_timestamp INTEGER NOT NULL,
    size INTEGER NOT NULL, 
    c_type INTEGER NOT NULL, 
    description TEXT NOT NULL, 
    version TEXT NOT NULL, 
    body BLOB NOT NULL, 
    UNIQUE(c_type, version)
)
"#;

pub async fn init() -> Result<(), SqlxErr> {

    get_pool().execute(CREATE_SQL).await?;

    Ok(())
}

#[derive(Debug, Serialize)]
pub struct Firm {
    id: i64,
    name: String,
    create_timestamp: i64,
    size: i64,
    description: String,
    version: String,
}

pub async fn del(id: i64) -> Result<(), SqlxErr> {

    sqlx::query(r#"DELETE FROM tb_firm WHERE id = ?"#)
        .bind(id)
        .execute(get_pool())
        .await?;

    Ok(())
}

pub async fn get(id: i64) -> Result<Download, AppErr> {

    let row = sqlx::query(r#"SELECT name, body FROM tb_firm WHERE id = ?"#)
    .bind(id)
    .fetch_optional( get_pool() )
    .await?
    .ok_or(new_err("no firm"))?;

    let download = new_download(row.get(0), row.get(1));

    Ok(download)
}

pub async fn query(c_type: u8) -> Result<Box<[Firm]>, SqlxErr> {

    let rows = sqlx::query(r#"
        SELECT id, name, create_timestamp, size, description, version FROM tb_firm WHERE c_type = ? ORDER BY create_timestamp DESC
    "#)
    .bind(c_type)
    .fetch_all( get_pool() )
    .await?;

    let mut vec = Vec::with_capacity(rows.len());
    for row in rows {
        let firm = Firm {
            id: row.get(0),
            name: row.get(1),
            create_timestamp: row.get(2),
            size: row.get(3),
            description: row.get(4),
            version: row.get(5)
        };
        vec.push(firm);
    }
    Ok(vec.into_boxed_slice())
}

// C_TYPE 0: 上位机
// C_TYPE 1: 下位机
pub async fn insert(name: &str, c_type: u8, description: &str, version: &str, body: &[u8]) -> Result<i64, SqlxErr> {
    let row = sqlx::query(r#"
        INSERT INTO tb_firm (
            name, 
            create_timestamp,
            size,
            c_type,
            description,
            version,
            body
        ) VALUES (?, ?, ?, ?, ?, ?, ?)
    "#)
    .bind(name)
    .bind( current_timestamp() as i64 )
    .bind( body.len() as i64 )
    .bind( c_type )
    .bind( description )
    .bind( version )
    .bind( body )
    .execute( get_pool() )
    .await?;

    Ok(row.last_insert_rowid())
}










